package com.study.jdbc.junit;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileReader;
import java.io.InputStream;
import java.io.Reader;
import java.sql.Blob;
import java.sql.Clob;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;
import java.util.UUID;

import javax.sql.rowset.serial.SerialBlob;
import javax.sql.rowset.serial.SerialClob;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.ArrayHandler;
import org.apache.commons.dbutils.handlers.ArrayListHandler;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ColumnListHandler;
import org.apache.commons.dbutils.handlers.KeyedHandler;
import org.apache.commons.dbutils.handlers.MapHandler;
import org.apache.commons.dbutils.handlers.MapListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import org.junit.Test;

import com.study.jdbc.bean.Customer;
import com.study.jdbc.utils.DBCPUtil;

/**
* @Name: DBUtilsTest
* @Description: DBUtils工具测试类
* @Author: 李超	lc_jack@foxmail.com
* @CreateDate: 2017-2-19 下午10:53:19 
* @Version: V1.0
 */
public class DBUtilsTest {
	
	private QueryRunner queryRunner = new QueryRunner(DBCPUtil.getDataSource()) ;
	
	//===========================QueryRunner更新（增、删、改）操作========================
	
	/**
	* @Name: updateRecord
	* @Description: 更新记录操作
	* @Author: 李超	lc_jack@foxmail.com
	* @Version: V1.0
	* @CreateDate: 2017-2-19 下午10:59:55
	* @Parameters: 
	* @Return: void
	 */
	@Test
	public void updateRecord() throws SQLException {
		String sql = "update t_customer set name = ? where id = ? ; " ;
		Customer customer = new Customer() ;
		customer.setId("2afb76ca-f2c1-4ef6-91e9-9c37f14fefe0") ;
		customer.setName("王五02") ;
		queryRunner.update(sql, 
				customer.getName(),
				customer.getId()) ;
	}
	
	/**
	* @Name: insertRecord
	* @Description: 新增记录操作
	* @Author: 李超	lc_jack@foxmail.com
	* @Version: V1.0
	* @CreateDate: 2017-2-19 下午11:00:41
	* @Parameters: 
	* @Return: void
	 */
	@Test
	public void insertRecord() throws Exception {
		String sql = "insert into t_customer(id, name, gender, birthday, cellphone, email, preference, type, description)" +
				"values(?, ?, ?, ?, ?, ?, ?, ?, ?) ;" ;
		Customer customer = new Customer() ;
		customer.setId(UUID.randomUUID().toString()) ;
		customer.setName("测试") ;
		customer.setGender("男") ;
		customer.setBirthday(new Date()) ;
		customer.setCellphone("1232131231") ;
		customer.setEmail("123@123.com") ;
		customer.setPreference("测试") ;
		customer.setType("普通客户") ;
		customer.setDescription("测试") ;
		queryRunner.update(sql, 
				customer.getId(),
				customer.getName(),
				customer.getGender(),
				customer.getBirthday(),
				customer.getCellphone(),
				customer.getEmail(),
				customer.getPreference(),
				customer.getType(),
				customer.getDescription()) ;
		System.out.println("插入成功！");
	}
	
	/**
	* @Name: deleteRecord
	* @Description: 删除记录操作
	* @Author: 李超	lc_jack@foxmail.com
	* @Version: V1.0
	* @CreateDate: 2017-2-19 下午11:00:49
	* @Parameters: 
	* @Return: void
	 */
	@Test
	public void deleteRecord() throws Exception {
		String sql = "delete from t_customer where id = ? ; " ;
		String id = "fafc3ff9-e030-4e06-9753-0d0bed2b56e5" ;
		queryRunner.update(sql, id) ;
		System.out.println("删除成功！");
	}
	
	//=========================QueryRunner查询操作：DBUtils中的各种结果处理器======================
	
	/**
	* @Name: testBeanHandler
	* @Description: BeanHandler结果处理器：查询一条记录，并将结果封装到JavaBean中
	* 约束：JavaBean中的属性必须与查询字段保持一致
	* @Author: 李超	lc_jack@foxmail.com
	* @Version: V1.0
	* @CreateDate: 2017-2-19 下午10:54:08
	* @Parameters: 
	* @Return: void
	 */
	@Test
	public void testBeanHandler() throws Exception {
		String sql = "select id, name, gender, birthday, cellphone, email, preference, type, description " +
				"from t_customer " +
				"where id = ? ; " ;
		String id = "146dedb9-ca8f-4ad4-991e-706380ff59c4" ;
		Customer customer = queryRunner.query(sql, new BeanHandler<Customer>(Customer.class), id) ;
		System.out.println(customer);
	}
	
	/**
	* @Name: testBeanListHandler
	* @Description: BeanListHandler结果处理器：查询多条记录，每条记录保存到指定的JavaBean中，并将结果封装到List<JavaBean>中
	* @Author: 李超	lc_jack@foxmail.com
	* @Version: V1.0
	* @CreateDate: 2017-2-19 下午10:58:23
	* @Parameters: 
	* @Return: void
	 */
	@Test
	public void testBeanListHandler() throws Exception {
		String sql = "select id, name, gender, birthday, cellphone, email, preference, type, description " +
				"from t_customer  ; " ;
		List<Customer> list = queryRunner.query(sql, new BeanListHandler<Customer>(Customer.class)) ;
		for (Customer customer : list) {
			System.out.println(customer);
		}
	}
	
	/**
	* @Name: testArrayHandler
	* @Description: ArrayHandler结果处理器：查询一条记录，并将查询记录中的每一个字段结果封装到数组中
	* @Author: 李超	lc_jack@foxmail.com
	* @Version: V1.0
	* @CreateDate: 2017-2-21 下午9:41:12
	* @Parameters: 
	* @Return: void
	 */
	@Test
	public void testArrayHandler() throws Exception {
		String sql = "select id, name, gender, birthday, cellphone, email, preference, type, description " +
				"from t_customer " +
				"where id = ? ; " ;
		String id = "146dedb9-ca8f-4ad4-991e-706380ff59c4" ;
		Object[] result = queryRunner.query(sql, new ArrayHandler(), id) ;
		//将查询结果封装到JavaBean中
		Customer customer = objectArray2JavaBean(result);
		System.out.println(customer);
	}

	/**
	* @Name: testArrayListHandler
	* @Description: ArrayListHandler结果处理器：查询多条记录，并将每条记录中的字段封装到数组中，再将每个数组封装到List<Object[]>中
	* @Author: 李超	lc_jack@foxmail.com
	* @Version: V1.0
	* @CreateDate: 2017-2-21 下午9:46:59
	* @Parameters: 
	* @Return: void
	 */
	@Test
	public void testArrayListHandler() throws Exception {
		String sql = "select id, name, gender, birthday, cellphone, email, preference, type, description " +
				"from t_customer  ; " ;
		List<Object[]> result = queryRunner.query(sql, new ArrayListHandler()) ;
		List<Customer> list = new ArrayList<Customer>() ;
		for (Object[] objects : result) {
			Customer customer = objectArray2JavaBean(objects) ;
			list.add(customer) ;
		}
		for (Customer customer : list) {
			System.out.println(customer);
		}
	}
	
	/**
	* @Name: testColumnListHandler
	* @Description: ColumnListHandler结果处理器：查询多条记录，并将指定的某个字段的查询结果存放到List<字段类型>中
	* @Author: 李超	lc_jack@foxmail.com
	* @Version: V1.0
	* @CreateDate: 2017-2-21 下午9:52:27
	* @Parameters: 
	* @Return: void
	 */
	@Test
	public void testColumnListHandler() throws Exception {
		String sql = "select id, name, gender, birthday, cellphone, email, preference, type, description " +
				"from t_customer  ; " ;
		//查询所有的名称字段，并将结果封装到List<字段类型>集合中
		List<Object> result = queryRunner.query(sql, new ColumnListHandler("name")) ;
		for (Object object : result) {
			System.out.println(object);
		}
	}
	
	/**
	* @Name: testKeyedHandler
	* @Description: KeyedHandler(name)：查询多条记录，并将每条记录封装到Map<列名，列值>中，再把每个Map存放到一个Map<指定的key，Map<列名，列值>>中
	* @Author: 李超	lc_jack@foxmail.com
	* @Version: V1.0
	* @CreateDate: 2017-2-21 下午10:00:51
	* @Parameters: 
	* @Return: void
	 */
	@Test
	public void testKeyedHandler() throws Exception {
		String sql = "select id, name, gender, birthday, cellphone, email, preference, type, description " +
				"from t_customer  ; " ;
		Map<Object, Map<String, Object>> result = queryRunner.query(sql, new KeyedHandler("name")) ;
		for(Map.Entry<Object, Map<String, Object>> entry : result.entrySet()) {
			Object key = entry.getKey() ;
			System.out.println("=====================================");
			System.out.println("key:" + key);
			System.out.println("value:");
			Map<String, Object> value = entry.getValue() ;
			for(Map.Entry<String, Object> entry2 : value.entrySet()) {
				System.out.println(">>>>>>" + entry2.getKey() + ":" + entry2.getValue());
			}
		}
	}
	
	/**
	* @Name: testMapHandler
	* @Description: MapHandler结果处理器：查询一条记录，并将查询结果封装到Map<列名，列值>中
	* @Author: 李超	lc_jack@foxmail.com
	* @Version: V1.0
	* @CreateDate: 2017-2-21 下午10:10:26
	* @Parameters: 
	* @Return: void
	 */
	@Test
	public void testMapHandler() throws Exception {
		String sql = "select id, name, gender, birthday, cellphone, email, preference, type, description " +
				"from t_customer " +
				"where id = ? ; " ;
		String id = "146dedb9-ca8f-4ad4-991e-706380ff59c4" ;
		Map<String, Object> result = queryRunner.query(sql, new MapHandler(), id) ;
		for(Map.Entry<String, Object> entry : result.entrySet()) {
			System.out.println(entry.getKey() + ":" + entry.getValue());
		}
	}
	
	/**
	* @Description: MapListHandler结果处理器：查询多条记录，并将每条记录封装到Map<列名，列值>中，再将Map封装到List<Map<列名，列值>>中
	* @Author: 李超	lc_jack@foxmail.com
	* @Version: V1.0
	* @CreateDate: 2017-2-21 下午10:13:34
	* @Parameters: 
	* @Return: void
	 */
	@Test
	public void testMapListHandler() throws Exception {
		String sql = "select id, name, gender, birthday, cellphone, email, preference, type, description " +
				"from t_customer  ; " ;
		List<Map<String, Object>> list = queryRunner.query(sql, new MapListHandler()) ;
		for (Map<String, Object> map : list) {
			System.out.println("=================================");
			for(Map.Entry<String, Object> entry : map.entrySet()) {
				System.out.println(entry.getKey() + ":" + entry.getValue());
			}
		}
	}
	
	/**
	* @Name: testScalarHandler
	* @Description: ScalarHandler结果处理器：查询结果为一列一行
	* @Author: 李超	lc_jack@foxmail.com
	* @Version: V1.0
	* @CreateDate: 2017-2-21 下午10:17:54
	* @Parameters: 
	* @Return: void
	 */
	@Test
	public void testScalarHandler() throws Exception {
		String sql = "select count(*) from t_customer ; " ;
		Object result = queryRunner.query(sql, new ScalarHandler()) ;
		System.out.println("共有：" + result + "条记录。");
	}
	
	/**
	* @Name: objectArray2JavaBean
	* @Description: 将查询结果Object[]数组封装到Customer
	* @Author: 李超	lc_jack@foxmail.com
	* @Version: V1.0
	* @CreateDate: 2017-2-21 下午9:50:17
	* @Parameters: @param result
	* @Parameters: @return
	* @Return: Customer
	 */
	private Customer objectArray2JavaBean(Object[] result) {
		Customer customer = new Customer() ;
		customer.setId((String)result[0]) ;
		customer.setName((String)result[1]) ;
		customer.setGender((String)result[2]) ;
		customer.setBirthday((Date)result[3]) ;
		customer.setCellphone((String)result[4]) ;
		customer.setEmail((String)result[5]) ;
		customer.setPreference((String)result[6]) ;
		customer.setType((String)result[7]) ;
		customer.setDescription((String)result[8]) ;
		return customer;
	}
	
	/**
	* @Name: saveClob
	* @Description: 使用DBUtils存储大文本字符数据
	* mysql数据库类型-对应-java数据类型：
	* 	sql-Text类型 -对应-> java-Clob类型
	* 	sql-Blob类型-对应-> java-Blob类型
	* @Author: 李超	lc_jack@foxmail.com
	* @Version: V1.0
	* @CreateDate: 2017-2-19 下午8:29:59
	* @Parameters: 
	* @Return: void
	 */
	@Test
	public void saveClob() throws Exception {
		String sql = "insert into t_lob(id, bigText, image, vedio) values(?, ?, null, null) ; " ;
		File file = new File("lobFile/upload/log.txt") ;
		Reader reader = new FileReader(file) ;
		char[] ch = new char[(int) file.length()] ;	//数据量大时，内存可能会溢出，因此不推荐使用该方法，推荐使用PreparedStatement进行大数据操作
		reader.read(ch) ;	
		Clob clob = new SerialClob(ch);
		//使用DBUtils工具存储大文本数据
		queryRunner.update(sql, UUID.randomUUID().toString(), clob) ;
	}
	
	/**
	* @Name: saveBlob
	* @Description: 使用DBUtils存储二进制大数据（图片、视频、音频等）
	* mysql数据库类型-对应-java数据类型：
	* 	sql-Text类型 -对应-> java-Clob类型
	* 	sql-Blob类型-对应-> java-Blob类型
	* @Author: 李超	lc_jack@foxmail.com
	* @Version: V1.0
	* @CreateDate: 2017-2-19 下午8:43:32
	* @Parameters: 
	* @Return: void
	 */
	@Test
	public void saveBlob() throws Exception {
		String sql = "insert into t_lob(id, bigText, image, vedio) values(?, null, ?, null) ; " ;
		File file = new File("lobFile/upload/HelloWorld.png") ;
		byte[] buf = new byte[(int) file.length()] ; //数据量大时，内存可能会溢出，因此不推荐使用该方法，推荐使用PreparedStatement进行大数据操作
		InputStream is = new FileInputStream(file) ;
		is.read(buf) ;
		Blob blob = new SerialBlob(buf) ;
		//使用DBUtils工具存储二进制大数据内容
		queryRunner.update(sql, UUID.randomUUID().toString(), blob) ;
	}
	
	/**
	* @Name: testBatch
	* @Description: TODO
	* @Author: 李超	lc_jack@foxmail.com
	* @Version: V1.0
	* @CreateDate: 2017-2-21 下午10:24:48
	* @Parameters: 
	* @Return: void
	 */
	@Test
	public void testBatch() throws Exception {
		String sql = "insert into t_lob(id, content) values(?, ?) ; " ;
		Object[][] params = new Object[10][] ;
		for (int i = 0; i < params.length; i++) {
			String content = UUID.randomUUID().toString() ;
 			params[i] = new Object[]{String.valueOf(i+1), content} ;
		}
		queryRunner.batch(sql, params) ;
	}
	
	

}








